MySQL查询重复记录的最新一条记录

2024-07-09 09:52| 来源: 网络整理| 查看: 265

一、背景

二、测试环境准备

三、基本SQL语句

四、参考文献

一、背景

最近在做一个黑名单的需求，但是由于历史原因，黑名单有大量的重复数据，比如一个user_id = 001 的用户，有效status=1,无效status=0,但是实际产线数据库有4条数据（3条无效，1条有效），故需要找出重复数据的最新一条数据，并将user_id设置为索引，不允许重复。

二、测试环境准备

新建表t_user，并插入测试数据（5条重复数据，5条非重复数据）

DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户编码', `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户名', `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '密码', `status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '状态', `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 134 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '用户信息表' ROW_FORMAT = COMPACT; -- ---------------------------- -- Records of t_user 5条重复数据，5条非重复数据 -- ---------------------------- INSERT INTO `t_user` VALUES (2, 'u_001', 'liudehua', '123456', 0, '2022-01-06 09:18:40', '2022-01-06 09:18:40'); INSERT INTO `t_user` VALUES (3, 'u_933', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40'); INSERT INTO `t_user` VALUES (4, 'u_586', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40'); INSERT INTO `t_user` VALUES (5, 'u_662', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40'); INSERT INTO `t_user` VALUES (6, 'u_89', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40'); INSERT INTO `t_user` VALUES (7, 'u_8476', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40'); INSERT INTO `t_user` VALUES (8, 'u_967', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40'); INSERT INTO `t_user` VALUES (124, 'u_001', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42'); INSERT INTO `t_user` VALUES (125, 'u_933', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42'); INSERT INTO `t_user` VALUES (126, 'u_586', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42'); INSERT INTO `t_user` VALUES (127, 'u_662', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42'); INSERT INTO `t_user` VALUES (128, 'u_89', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42'); INSERT INTO `t_user` VALUES (130, 'u_96', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42'); INSERT INTO `t_user` VALUES (131, 'u_449', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42'); INSERT INTO `t_user` VALUES (132, 'u_4330', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42'); 三、基本SQL语句

查询数量

SELECT count(*) from t_user; -- 查询数量

查询所有重复数据的user_id以及重复条数

SELECT u.user_id, COUNT(*) from t_user u group by u.user_id HAVING COUNT(1) > 1; -- 查询重复的数据以及重复条数

拓展：查询所有不重复数据的user_id

SELECT u.user_id from t_user u group by u.user_id HAVING COUNT(1) = 1; -- 查询不重复的数据

查询所有重复数据

SELECT * from t_user u2 where u2.user_id in( SELECT u.user_id from t_user u group by u.user_id HAVING COUNT(1) > 1); -- 查询所有重复的数据

查询最新一条数据

SELECT * from t_user where id = (SELECT MAX(id) from t_user); -- 查询最新一条数据

根据时间查询重复记录的最新一条数据（这个SQL有问题，因为如果update_time相同的话，将会导致结果仍旧有重复数据，我们这个demo就是这个情况）

SELECT u.user_id, u.username, u.create_time, u.update_time from t_user u where u.update_time in (SELECT MAX(u2.update_time) from t_user u2 group by u2.user_id); -- 根据时间查询最新的数据（查询结果为15条，实际为10条，所以有问题）

根据id查询记录的最新一条数据（因为id是主键，是不会重复的，所以没有问题）

-- 方案一 SELECT * from t_user u where u.id in (SELECT MAX(u2.id) from t_user u2 group by u2.user_id); -- 方案二 SELECT * FROM t_user t WHERE EXISTS (SELECT 1 FROM t_user GROUP BY user_id HAVING MAX(id) = t.id); -- 根据id查询最新的数据（记录为10条）

拓展：查询重复数据的第一条记录

SELECT u.user_id, u.username, u.create_time, u.update_time from t_user u where u.id in (SELECT min(u2.id) from t_user u2 group by u2.user_id); -- 根据id查询第一条记录（记录为10条）四、参考文献

1、mysql查询出所有重复的记录

https://www.cnblogs.com/phpfensi/p/10214487.html

【本文地址】

公司简介

联系我们